#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import sys, datetime
import pandas as pd
import numpy as np
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from xpy3lib.utils.XDateTimeUtils import XDateTimeUtils

from sicost.dp_common_job import DP011MJob__df7


class CA0101Job(AbstractDPJob):
    """
    """


    data_type = None



    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center_org:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        :param p_wce_org: 成本科目编号
        :param p_wce:
        :param p_cal_type:
        :param p_coef:
        :param p_mon:
        """
        super(CA0101Job, self).__init__(p_config=p_config,
                                            p_db_conn_mpp=p_db_conn_mpp,
                                            p_db_conn_rds=p_db_conn_rds,
                                            p_db_conn_dbprod7=p_db_conn_dbprod7,
                                            p_unit=p_unit,
                                            p_account=p_account,
                                            p_cost_center=p_cost_center,
                                            p_account_period_start=p_account_period_start,
                                            p_account_period_end=p_account_period_end)

        self.data_type = p_data_type


    def do_execute(self):
        """
        """
        self.logger.info('CA0101Job.do_execute')

        self.__step_0()

        sql = " select  HEX((RAND()))||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID,A.ACCT as ACCOUNT, A.DEPARTMENT_CODE as FACTORY,A.COST_CENTER,A.UNIT_CODE as UNIT, A.CLASS as TEAM,A.SHIFT,A.PRODUCE_TIME as WORK_TIME, A.PRODUCE_START_TIME as PROCESS_START_TIME, A.PRODUCE_END_TIME as PROCESS_END_TIME,A.BYPRODUCT_CODE as PRODUCT_CODE,A.STEELNO as ST_NO, A.MAT_ACT_WIDTH, A.WIDTH,A.WIDTH_CODE as WIDTH_COD,A.MAT_ACT_THICK,A.THICK,A.THICK_CODE as THICK_COD,A.PROD_COILNO as MAT_NO,A.ITEM_ID,A.PLAN_SYS_KV_CODE as JHZ_KEY,A.PLAN_SYS_KV_CODE_NAME as JHZ_KEY_NAME,A.INPUT_BYPRODUCT_CODE as IN_PRODUCT_CODE,A.ENTRY_COILNO as IN_MAT_NO, A.OUTPUT_WT as WT, " \
              " A.ACT_OUTPUT_WT as ACT_WT,A.INPUT_WT as IN_WT,A.ACT_INPUT_WT as ACT_IN_WT, A.COST_SUBJECT as WCE,A.COST_SUBJECT_ON_AMT as ACT_N, 0 AS ACT_AMT,A.APPTHROWAIMODE as APP_THROW_AI_MODE,A.COATING_TYPE as LAYER_TYPE,A.MAT_ACT_LEN,A.MAT_ACT_AREA, A.TOP_COATING_WT as TOP_COAT_WT,A.BOT_COATING_WT as BOT_COAT_WT, A.DATA_TYPE, 'Y' AS TYPE,A.ANNEAL_CURVE as DESIGN_ANNEAL_DIAGRAM_CODE,A.TRIM_FLAG,A.ENTRY_MAT_WIDTH as IN_MAT_WIDTH,A.ENTRY_WIDTH as IN_WIDTH, A.ENTRY_WIDTH_CODE as IN_WIDTH_COD,A.ENTRY_MAT_THICK as IN_MAT_THICK,A.ENTRY_THICK as IN_THICK, A.ENTRY_THICK_CODE as IN_THICK_COD, " \
              " A.PLAN_NO, A.PLAN_NO_C, A.PLAN_NO_CODE as PLAN_NO_COD,A.TRIMM_WIDTH as TRIM_WIDTH, A.TRIMMING_WIDTH_RANGE as TRIM_WIDTH_C,A.TRIMMING_WIDTH_CODE as TRIM_WIDTH_COD,A.ENTRY_MAT_INDIA as IN_MAT_INNER_DIA,A.IN_MAT_DIV_CODE as IN_MAT_DIV_CODE,A.IN_MAT_DIV as IN_MAT_DIV,A.PRODUCE_NICK_FLAG as LAS_NOTCH_FLAG,A.PROD_CAT_CODE as QS_CODE,A.PROD_CAT as QS_NAME  " \
              " from ( " \
              " select DISTINCT ACCT, DEPARTMENT_CODE, COST_CENTER,UNIT_CODE, CLASS,SHIFT, PRODUCE_TIME, PRODUCE_START_TIME, PRODUCE_END_TIME, " \
              " BYPRODUCT_CODE,STEELNO,MAT_ACT_WIDTH,WIDTH,WIDTH_CODE,MAT_ACT_THICK,THICK,THICK_CODE,PROD_COILNO,ITEM_ID,PLAN_SYS_KV_CODE,PLAN_SYS_KV_CODE_NAME, INPUT_BYPRODUCT_CODE, ENTRY_COILNO, " \
              " SUM(COALESCE(OUTPUT_WT,0)) AS OUTPUT_WT,SUM(COALESCE(ACT_OUTPUT_WT,0)) AS ACT_OUTPUT_WT,SUM(COALESCE(INPUT_WT,0)) AS INPUT_WT,SUM(COALESCE(ACT_INPUT_WT,0)) AS ACT_INPUT_WT, " \
              " COST_SUBJECT , SUM(COALESCE(COST_SUBJECT_ON_AMT,0)) AS COST_SUBJECT_ON_AMT, " \
              " APPTHROWAIMODE,COATING_TYPE,MAT_ACT_LEN,MAT_ACT_AREA,TOP_COATING_WT,BOT_COATING_WT,DATA_TYPE, " \
              " ANNEAL_CURVE,TRIM_FLAG,ENTRY_MAT_WIDTH,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_MAT_THICK,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO,PLAN_NO_C,PLAN_NO_CODE, " \
              " TRIMM_WIDTH,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,ENTRY_MAT_INDIA,IN_MAT_DIV_CODE,IN_MAT_DIV,PRODUCE_NICK_FLAG,PROD_CAT_CODE,PROD_CAT " \
              " from  BGTARAS1.T_ADS_FACT_SICB_DP0103 " \
              " where  1=1 and ACCT = '%s' and COST_CENTER = '%s' AND UNIT_CODE = '%s' " \
              " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
              " AND LEFT(PRODUCE_TIME,14) < '%s' " \
              " AND DATA_TYPE = '%s'  AND LEFT(COST_SUBJECT,2) != '15'  " \
              " group by ACCT,DEPARTMENT_CODE,COST_CENTER,UNIT_CODE,CLASS,SHIFT,PRODUCE_TIME, PRODUCE_START_TIME, PRODUCE_END_TIME, " \
              " BYPRODUCT_CODE,STEELNO,MAT_ACT_WIDTH,WIDTH,WIDTH_CODE,MAT_ACT_THICK,THICK,THICK_CODE,PROD_COILNO,ITEM_ID,PLAN_SYS_KV_CODE,PLAN_SYS_KV_CODE_NAME, INPUT_BYPRODUCT_CODE, ENTRY_COILNO,COST_SUBJECT,APPTHROWAIMODE,COATING_TYPE,MAT_ACT_LEN,MAT_ACT_AREA,TOP_COATING_WT,BOT_COATING_WT,DATA_TYPE,ANNEAL_CURVE,TRIM_FLAG,ENTRY_MAT_WIDTH,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_MAT_THICK,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO,PLAN_NO_C,PLAN_NO_CODE, " \
              " TRIMM_WIDTH,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,ENTRY_MAT_INDIA,IN_MAT_DIV_CODE,IN_MAT_DIV,PRODUCE_NICK_FLAG,PROD_CAT_CODE,PROD_CAT) as A " % (self.account, self.cost_center, self.unit, self.account_period_start, self.account_period_end, self.data_type)

        # " where b.max_end_time>'%s' AND b.in_mat_no_1='10847939200' " \

        self.logger.info(sql)
        df_A = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df_A.empty is False
        self.logger.info(success)
        if success is False:
            return

        df_A.columns = df_A.columns.str.upper()
        self.logger.info(df_A)
        df_A['WORK_MONTH'] = df_A['WORK_TIME'].str[4:6]
        df_A['WORK_YEAR'] = df_A['WORK_TIME'].str[0:4]

        df_A['WORK_YEAR_MONTH'] = df_A['WORK_TIME'].str[0:6]
        def __cal_DATA_TYPE_2(x):

            if x.WORK_MONTH == '01':
                rst = 'Y'
            else:
                rst = 'M'
            return rst
        df_A['DATA_TYPE_2'] = df_A.apply(lambda x: __cal_DATA_TYPE_2(x), axis=1)
        df_A['DATA_TYPE_3'] = 'Y'
        def __cal_EDITION(x):

            if x.WORK_MONTH == '01':
                rst = str(int(x.WORK_YEAR)-1)
            else:
                rst = str(int(x.WORK_YEAR_MONTH)-1)
            return rst
        df_A['EDITION'] = df_A.apply(lambda x: __cal_EDITION(x), axis=1)

        def __cal_EDITION_2(x):

            rst = str(int(x.WORK_YEAR) - 1)

            return rst

        df_A['EDITION_2'] = df_A.apply(lambda x: __cal_EDITION_2(x), axis=1)

        # sql = " select " \
        #       " ACCT as ACCOUNT,EDITION,MAX(COALESCE(FLAG,'N'))AS FLAG  " \
        #       " FROM BGTAMAS1.T_ADS_WH_SICB_DP0401 " \
        #       " WHERE 1=1 and DATA_TYPE='%s' and FLAG='Y'  " \
        #       " GROUP BY ACCT,EDITION,DATA_TYPE " % (self.data_type)
        #
        #
        #
        #
        #
        # self.logger.info(sql)
        # #df_B = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        # df_B = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        # df_B.columns = df_B.columns.str.upper()
        # self.logger.info(df_B)
        #
        #
        # df_AB = pd.merge(df_A, df_B, on='ACCOUNT', how='inner')
        # self.logger.info(df_AB)
        sql = " select ACCT as ACCOUNT,DATE as EDITION,COST_CENTER,COST_SUBJECT as WCE,DATA_TYPE as DATA_TYPE_2, " \
              " (CASE when REVISE_VALUE is not null then REVISE_VALUE else PRICE end)as PRICE_1,'Y' as FLAG  " \
              " from BGTARAS1.T_ADS_WH_SICB_WCEPRICE " \
              " WHERE 1=1   " \
              " and COST_CENTER='%s' " % (self.cost_center)
        # sql = " select COALESCE (PRICE, 1) AS PRICE,EDITION,ACCOUNT,DATA_TYPE,COST_CENTER,WCE FROM BGRAGGCB.SU_AJBG_DP0401"
        # sql = " select COALESCE (PRICE, 1) AS PRICE,EDITION,ACCT as ACCOUNT,DATA_TYPE,COST_CENTER,COST_SUBJECT as WCE FROM BGTAMAS1.T_ADS_FACT_SICB_DP0401"
        self.logger.info(sql)
        # df_C = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        #df_C = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        df_C = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        df_C.columns = df_C.columns.str.upper()
        self.logger.info(df_C)
        # v = ['EDITION',
        #      'ACCOUNT',
        #      'DATA_TYPE',
        #      'COST_CENTER',
        #      'WCE']
        v = ['EDITION',
             'ACCOUNT',
             'COST_CENTER',
             'WCE','DATA_TYPE_2']

        df_DP0101 = pd.merge(df_A, df_C, on=v, how='left')
        sql = " select ACCT as ACCOUNT,DATE as EDITION_2,COST_CENTER,COST_SUBJECT as WCE,DATA_TYPE as DATA_TYPE_3, " \
              " (CASE when REVISE_VALUE is not null then REVISE_VALUE else PRICE end)as PRICE_2  " \
              " from BGTARAS1.T_ADS_WH_SICB_WCEPRICE " \
              " WHERE 1=1   " \
              " and COST_CENTER='%s' " % (self.cost_center)
        self.logger.info(sql)
        df_CC = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        df_CC.columns = df_CC.columns.str.upper()
        v = ['EDITION_2',
             'ACCOUNT',
             'COST_CENTER',
             'WCE', 'DATA_TYPE_3']

        df_DP0101 = pd.merge(df_DP0101, df_CC, on=v, how='left')
        df_DP0101['PRICE_1'].fillna(value='', inplace=True)

        def __cal_PRICE_0000(x):

            if x.PRICE_1 == '' :
                rst = x.PRICE_2
            else:
                rst = x.PRICE_1
            return rst

        df_DP0101['PRICE'] = df_DP0101.apply(lambda x: __cal_PRICE_0000(x), axis=1)
        df_DP0101['UNIT_AMT'] = df_DP0101['ACT_N'] * df_DP0101['PRICE']
        df_DP0101['FAC_AMT'] = df_DP0101['ACT_N'] * df_DP0101['PRICE']
        df_DP0101.drop(['DATA_TYPE_2'], axis=1, inplace=True)
        df_DP0101.drop(['DATA_TYPE_3'], axis=1, inplace=True)
        df_DP0101.drop(['EDITION_2'], axis=1, inplace=True)
        df_DP0101.drop(['PRICE_1'], axis=1, inplace=True)
        df_DP0101.drop(['PRICE_2'], axis=1, inplace=True)
        df_DP0101.drop(['WORK_MONTH'], axis=1, inplace=True)
        df_DP0101.drop(['WORK_YEAR'], axis=1, inplace=True)
        df_DP0101.drop(['WORK_YEAR_MONTH'], axis=1, inplace=True)

        def __cal_UNIT_AMT_000(x):
            rst = 0
            if x.ACT_WT > 0:
                rst = x.ACT_N * x.PRICE
            else:
                rst = x.ACT_N * x.PRICE
                rst = abs(rst)
                rst = -1 * rst
            return rst

        df_DP0101['UNIT_AMT'] = df_DP0101.apply(lambda x: __cal_UNIT_AMT_000(x), axis=1)

        def __cal_FAC_AMT_000(x):
            rst = 0
            if x.ACT_WT > 0:
                rst = x.ACT_N * x.PRICE
            else:
                rst = x.ACT_N * x.PRICE
                rst = abs(rst)
                rst = -1 * rst
            return rst

        df_DP0101['FAC_AMT'] = df_DP0101.apply(lambda x: __cal_FAC_AMT_000(x), axis=1)


        self.logger.info(df_DP0101)
        cost_center = self.cost_center
        self.logger.info(cost_center[0:2])

        if cost_center[0:2] == 'MI':
            sql = " SELECT HEX((RAND()))||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID,A.ACCT as ACCOUNT ,A.DEPARTMENT_CODE as FACTORY,A.COST_CENTER ,A.UNIT_CODE as UNIT ,A.CLASS as TEAM,A.SHIFT,A.PRODUCE_TIME as WORK_TIME ,A.PRODUCE_START_TIME as PROCESS_START_TIME,A.PRODUCE_END_TIME as PROCESS_END_TIME,A.BYPRODUCT_CODE as PRODUCT_CODE,A.STEELNO as ST_NO,A.MAT_ACT_WIDTH,A.WIDTH,A.WIDTH_CODE as WIDTH_COD,A.MAT_ACT_THICK,A.THICK,A.THICK_CODE as THICK_COD,A.PROD_COILNO as MAT_NO " \
                  " ,A.ITEM_ID,A.PLAN_SYS_KV_CODE as JHZ_KEY,A.PLAN_SYS_KV_CODE_NAME as JHZ_KEY_NAME,A.INPUT_BYPRODUCT_CODE as IN_PRODUCT_CODE,A.ENTRY_COILNO as IN_MAT_NO,A.OUTPUT_WT as WT,A.ACT_OUTPUT_WT as ACT_WT,A.INPUT_WT as IN_WT ,A.ACT_INPUT_WT as ACT_IN_WT,A.COST_SUBJECT as WCE,A.COST_SUBJECT_ON_AMT as ACT_N ,A.APPTHROWAIMODE as APP_THROW_AI_MODE ,A.COATING_TYPE as LAYER_TYPE ,A.MAT_ACT_LEN,A.MAT_ACT_AREA ,A.TOP_COATING_WT as TOP_COAT_WT ,A.BOT_COATING_WT as BOT_COAT_WT  " \
                  " ,A.DATA_TYPE,A.ANNEAL_CURVE as DESIGN_ANNEAL_DIAGRAM_CODE ,A.TRIM_FLAG,A.ENTRY_MAT_WIDTH as IN_MAT_WIDTH ,A.ENTRY_WIDTH as IN_WIDTH ,A.ENTRY_WIDTH_CODE as IN_WIDTH_COD  ,A.ENTRY_MAT_THICK as IN_MAT_THICK,A.ENTRY_THICK as IN_THICK,A.ENTRY_THICK_CODE as IN_THICK_COD,A.PLAN_NO ,A.PLAN_NO_C,A.PLAN_NO_CODE as PLAN_NO_COD ,A.TRIMM_WIDTH as TRIM_WIDTH ,A.TRIMMING_WIDTH_RANGE as TRIM_WIDTH_C " \
                  " ,A.TRIMMING_WIDTH_CODE as TRIM_WIDTH_COD,A.ENTRY_MAT_INDIA as IN_MAT_INNER_DIA,A.IN_MAT_DIV_CODE ,A.IN_MAT_DIV,A.PRODUCE_NICK_FLAG as LAS_NOTCH_FLAG  ,A.PROD_CAT_CODE as QS_CODE ,A.PROD_CAT as QS_NAME " \
                  " FROM ( select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 WHERE 1=1 and ACCT = '%s' and COST_CENTER = '%s' AND UNIT_CODE = '%s' " \
                  " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
                  " AND LEFT(PRODUCE_TIME,14) < '%s' " \
                  " AND DATA_TYPE = '%s'" \
                  " AND LEFT(COST_SUBJECT,2) = '15' " \
                  " ) AS A " % (self.account, self.cost_center, self.unit, self.account_period_start, self.account_period_end, self.data_type)

            self.logger.info(sql)
            df_2A = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
            success = df_2A.empty is False
            self.logger.info(success)
            if success is False:
                return
            df_2A.columns = df_2A.columns.str.upper()
            self.logger.info(df_2A)
            sql = " select COST,COALESCE(COST,0) as B_COST,MAT_NO as IN_MAT_NO, COALESCE(MAT_SEQ,'0') as B_MAT_SEQ FROM BGRAGGCB.SU_WEBG_CJPE01"
            # sql = " select COST,COALESCE(COST,0) as B_COST,MAT_NO as IN_MAT_NO, COALESCE(MAT_SEQ,'0') as B_MAT_SEQ FROM M1_WE.SU_WEBG_CJPE01 WHERE ACCOUNT = '1001'  AND YMONTH IN (TO_CHAR(CURRENT TIMESTAMP, 'YYYYMM'),TO_CHAR(CURRENT TIMESTAMP-1 MONTH, 'YYYYMM')) AND SUBSTR(ST_NO,2,1) IN ('H','W') "
            self.logger.info(sql)
            df_2B = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            # df_2B = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
            # print(df_2B)
            df_2B.columns = df_2B.columns.str.upper()
            self.logger.info(df_2B)
            df_DP010201 = pd.merge(df_2A, df_2B, on='IN_MAT_NO', how='left')
            df_DP010201['COST'].fillna(value='', inplace=True)
            def __cal_TYPE(x):
                rst = 0
                if x.COST == '' or x.WCE not in ('15171','15993','15996'):
                    rst = 'N'
                else:
                    rst = 'Y'
                return rst

            df_DP010201['TYPE'] = df_DP010201.apply(lambda x: __cal_TYPE(x), axis=1)

            def __cal_ACT_AMT(x):
                rst = 0
                if x.COST == '' or x.WCE not in ('15171','15993','15996'):
                    rst = 0
                else:
                    rst = x.B_COST * x.ACT_IN_WT
                return rst

            df_DP010201['ACT_AMT'] = df_DP010201.apply(lambda x: __cal_ACT_AMT(x), axis=1)

            def __cal_UNIT_AMT(x):
                rst = 0
                if x.COST == '' or x.WCE not in ('15171','15993','15996'):
                    rst = 0
                else:
                    rst = x.B_COST * x.ACT_IN_WT
                return rst

            df_DP010201['UNIT_AMT'] = df_DP010201.apply(lambda x: __cal_UNIT_AMT(x), axis=1)

            def __cal_FAC_AMT(x):
                rst = 0
                if x.COST == '' or x.WCE not in ('15171','15993','15996'):
                    rst = 0
                else:
                    rst = x.B_COST * x.ACT_IN_WT
                return rst

            df_DP010201['FAC_AMT'] = df_DP010201.apply(lambda x: __cal_FAC_AMT(x), axis=1)

            def __cal_PRICE(x):
                rst = 0
                if x.COST == '' or x.WCE not in ('15171','15993','15996'):
                    rst = 0
                else:
                    rst = x.B_COST
                return rst

            df_DP010201['PRICE'] = df_DP010201.apply(lambda x: __cal_PRICE(x), axis=1)
            df_DP010201.drop(['COST'], axis=1, inplace=True)
            df_DP010201.drop(['B_COST'], axis=1, inplace=True)
            v = ['ACCOUNT',
                 'FACTORY',
                 'PRODUCT_CODE',
                 'COST_CENTER',
                 'UNIT',
                 'TEAM',
                 'WORK_TIME',
                 'MAT_NO',
                 'IN_MAT_NO',
                 'ACT_WT']

            df_DP010201['B_MAT_SEQ'] = df_DP010201['B_MAT_SEQ'].astype(str)
            def __cal_B_MAT_SEQ(x):
                rst = '1'
                if '1'in x.B_MAT_SEQ:
                    rst = x.B_MAT_SEQ
                else:
                    rst = '1'

                return rst

            df_DP010201['B_MAT_SEQ'] = df_DP010201.apply(lambda x: __cal_B_MAT_SEQ(x), axis=1)
            #df_DP010201['B_MAT_SEQ'] = df_DP010201['B_MAT_SEQ'].fillna('1')
            #
            try:
                # df_DP010201['B_MAT_SEQ_NEW'] = df_DP010201['B_MAT_SEQ'].long
                df_DP010201['B_MAT_SEQ'] = df_DP010201['B_MAT_SEQ'].astype(float)
                df_DP010201['NUM'] = df_DP010201.groupby(v)['B_MAT_SEQ'].rank(ascending=0, method='first')
            except Exception as e:
                self.logger.error(str(e))

            df_DP010201_1 = df_DP010201[df_DP010201['NUM'] == 1]
            v = ['ACCOUNT',
                 'FACTORY',
                 'PRODUCT_CODE',
                 'COST_CENTER',
                 'UNIT',
                 'TEAM',
                 'WORK_TIME',
                 'MAT_NO',
                 'IN_MAT_NO',
                 'ACT_WT']
            df_DP010201_1.drop_duplicates(subset=v, keep='first', inplace=True)

            df_DP010201_1.rename(columns={'ACT_AMT': 'OLD_ACT_AMT'}, inplace=True)
            df_DP010201_1.rename(columns={'UNIT_AMT': 'OLD_UNIT_AMT'}, inplace=True)
            df_DP010201_1.rename(columns={'FAC_AMT': 'OLD_FAC_AMT'}, inplace=True)
            df_DP010201_1.rename(columns={'PRICE': 'OLD_PRICE'}, inplace=True)


            df_DP010201_1['WORK_MONTH'] = df_DP010201_1['WORK_TIME'].str[4:6]
            df_DP010201_1['WORK_YEAR'] = df_DP010201_1['WORK_TIME'].str[0:4]

            df_DP010201_1['WORK_YEAR_MONTH'] = df_DP010201_1['WORK_TIME'].str[0:6]

            def __cal_DATA_TYPE_2(x):

                if x.WORK_MONTH == '01':
                    rst = 'Y'
                else:
                    rst = 'M'
                return rst

            df_DP010201_1['DATA_TYPE_2'] = df_DP010201_1.apply(lambda x: __cal_DATA_TYPE_2(x), axis=1)
            df_DP010201_1['DATA_TYPE_3'] = 'Y'
            def __cal_EDITION(x):

                if x.WORK_MONTH == '01':
                    rst = str(int(x.WORK_YEAR) - 1)
                else:
                    rst = str(int(x.WORK_YEAR_MONTH) - 1)
                return rst

            df_DP010201_1['EDITION'] = df_DP010201_1.apply(lambda x: __cal_EDITION(x), axis=1)

            def __cal_EDITION_2(x):

                rst = str(int(x.WORK_YEAR) - 1)

                return rst

            df_DP010201_1['EDITION_2'] = df_DP010201_1.apply(lambda x: __cal_EDITION_2(x), axis=1)
            #df_AB2 = pd.merge(df_DP010201_1, df_B, on='ACCOUNT', how='inner')
            #self.logger.info(df_AB2)
            # sql = " select COALESCE (PRICE, 0.0) AS PRICE,EDITION,ACCOUNT,DATA_TYPE,COST_CENTER,WCE FROM BGRAGGCB.SU_AJBG_DP0401"
            #sql = " select COALESCE (PRICE, 0.0) AS PRICE,EDITION,ACCT as ACCOUNT,DATA_TYPE,COST_CENTER,COST_SUBJECT as WCE FROM BGTAMAS1.T_ADS_FACT_SICB_DP0401"
            sql = " select ACCT as ACCOUNT,DATE as EDITION,COST_CENTER,COST_SUBJECT as WCE,DATA_TYPE as DATA_TYPE_2, " \
                  " (CASE when REVISE_VALUE is not null then REVISE_VALUE else PRICE end)as PRICE_1,'Y' as FLAG  " \
                  " from BGTARAS1.T_ADS_WH_SICB_WCEPRICE " \
                  " WHERE 1=1  " \
                  " and COST_CENTER='%s' " % (self.cost_center)
            self.logger.info(sql)
            df_C2 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
            df_C2.columns = df_C2.columns.str.upper()
            self.logger.info(df_C2)

            # v = ['EDITION',
            #      'ACCOUNT',
            #      'DATA_TYPE',
            #      'COST_CENTER',
            #      'WCE']
            v = ['EDITION',
                 'ACCOUNT',
                 'COST_CENTER',
                 'WCE','DATA_TYPE_2']

            df_DP0102LS = pd.merge(df_DP010201_1, df_C2, on=v, how='left')
            sql = " select ACCT as ACCOUNT,DATE as EDITION_2,COST_CENTER,COST_SUBJECT as WCE,DATA_TYPE as DATA_TYPE_3, " \
                  " (CASE when REVISE_VALUE is not null then REVISE_VALUE else PRICE end)as PRICE_2  " \
                  " from BGTARAS1.T_ADS_WH_SICB_WCEPRICE " \
                  " WHERE 1=1   " \
                  " and COST_CENTER='%s' " % (self.cost_center)
            self.logger.info(sql)
            df_CC = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
            df_CC.columns = df_CC.columns.str.upper()
            v = ['EDITION_2',
                 'ACCOUNT',
                 'COST_CENTER',
                 'WCE', 'DATA_TYPE_3']

            df_DP0102LS = pd.merge(df_DP0102LS, df_CC, on=v, how='left')
            df_DP0102LS['PRICE_1'].fillna(value='', inplace=True)

            def __cal_PRICE_0000(x):

                if x.PRICE_1 == '':
                    rst = x.PRICE_2
                else:
                    rst = x.PRICE_1
                return rst

            df_DP0102LS['PRICE'] = df_DP0102LS.apply(lambda x: __cal_PRICE_0000(x), axis=1)
            df_DP0102LS.drop(['DATA_TYPE_2'], axis=1, inplace=True)
            df_DP0102LS.drop(['DATA_TYPE_3'], axis=1, inplace=True)
            df_DP0102LS.drop(['EDITION_2'], axis=1, inplace=True)
            df_DP0102LS.drop(['PRICE_1'], axis=1, inplace=True)
            df_DP0102LS.drop(['PRICE_2'], axis=1, inplace=True)
            df_DP0102LS.drop(['WORK_MONTH'], axis=1, inplace=True)
            df_DP0102LS.drop(['WORK_YEAR'], axis=1, inplace=True)
            df_DP0102LS.drop(['WORK_YEAR_MONTH'], axis=1, inplace=True)
            self.logger.info(df_DP0102LS)
            def __cal_ACT_AMT_1(x):
                rst = 0
                if x.TYPE == 'N':
                    rst = 0
                    str1 = 'PN'
                    if str1 not in x.APP_THROW_AI_MODE:
                        rst = x.IN_WT * x.PRICE
                    else:
                        rst = x.ACT_N * x.PRICE

                else:
                    rst = x.OLD_ACT_AMT
                return rst

            df_DP0102LS['ACT_AMT'] = df_DP0102LS.apply(lambda x: __cal_ACT_AMT_1(x), axis=1)

            def __cal_UNIT_AMT_1(x):
                rst = 0
                if x.TYPE == 'N':
                    rst = x.ACT_N * x.PRICE
                else:
                    rst = x.OLD_UNIT_AMT
                return rst

            df_DP0102LS['UNIT_AMT'] = df_DP0102LS.apply(lambda x: __cal_UNIT_AMT_1(x), axis=1)

            def __cal_FAC_AMT_1(x):
                rst = 0
                if x.TYPE == 'N':
                    str1 = 'PN'
                    if str1 not in x.APP_THROW_AI_MODE:
                        rst = x.IN_WT * x.PRICE + x.ACT_N * x.PRICE
                    else:
                        rst = x.ACT_N * x.PRICE

                else:
                    rst = x.OLD_FAC_AMT
                return rst

            df_DP0102LS['FAC_AMT'] = df_DP0102LS.apply(lambda x: __cal_FAC_AMT_1(x), axis=1)

            def __cal_PRICE_1(x):
                rst = 0
                if x.TYPE == 'N':
                    rst = x.PRICE
                else:
                    rst = x.OLD_PRICE
                return rst

            df_DP0102LS['PRICE'] = df_DP0102LS.apply(lambda x: __cal_PRICE_1(x), axis=1)
            df_DP0102LS.drop(['OLD_ACT_AMT'], axis=1, inplace=True)
            df_DP0102LS.drop(['OLD_UNIT_AMT'], axis=1, inplace=True)
            df_DP0102LS.drop(['OLD_FAC_AMT'], axis=1, inplace=True)
            df_DP0102LS.drop(['OLD_PRICE'], axis=1, inplace=True)
            df_DP0102LS.drop(['NUM'], axis=1, inplace=True)
            df_DP0102LS.drop(['B_MAT_SEQ'], axis=1, inplace=True)

            self.logger.info(df_DP0102LS)
            df_CA0101 = pd.concat([df_DP0101, df_DP0102LS], ignore_index=True)
            self.logger.info(df_CA0101)
            df_DP0102LS_N = df_DP0102LS[df_DP0102LS['TYPE'] == 'N']


            # NOTE self.account_period_start ????????
            ttt = datetime.datetime.strptime(str(self.account_period_start), '%Y%m%tmp_dict%H%M%S')

            ten_days = datetime.timedelta(days=10)
            prev_day = ttt - ten_days
            e = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day, hour=20, minute=0,
                                  second=0)
            e = e.strftime('%Y%m%tmp_dict%H%M%S')



            self.logger.info(df_DP0102LS_N.empty)

            df_CA0101_Y = df_CA0101[df_CA0101['TYPE'] == 'Y']
            df_CA0101_Y['ROW'] = 1
            # df_CA0101_Y['INMAT'] = df_CA0101_Y['MAT_NO']
            df_DP0102LS['INMAT'] = df_DP0102LS['IN_MAT_NO']


            w = ['MAT_NO','IN_MAT_NO','WORK_TIME']

            a = df_CA0101_Y.groupby(w)['FAC_AMT'].agg([np.sum]).round(2)
            a.rename(columns={'sum': 'SUM_FAC_AMT'}, inplace=True)
            b = df_CA0101_Y.groupby(w)['ROW'].agg([np.sum]).round(2)
            b.rename(columns={'sum': 'SUM_ROW'}, inplace=True)
            c = df_CA0101_Y.groupby(w)['WT'].agg([np.sum]).round(2)
            c.rename(columns={'sum': 'SUM_WT'}, inplace=True)
            ab = pd.merge(a, b, on=w, how='left')
            abc = pd.merge(ab, c, on=w, how='left')
            d = df_CA0101_Y[['MAT_NO','IN_MAT_NO','WORK_TIME']]
            d.drop_duplicates(subset=w, keep='first', inplace=True)

            abcd = pd.merge(d, abc, on=w, how='left')

            v = ['MAT_NO']

            abcd['WORK_TIME_2'] = abcd['WORK_TIME'].astype(float)
            #abcd['IN_MAT_NO'] = abcd['IN_MAT_NO'].astype(float)
            #abcd['INMAT'] = abcd['INMAT'].astype(float)



            abcd['ROW_NUM_TMP'] = abcd.groupby(v)['WORK_TIME_2'].rank(ascending=False, method='first')

            abcd = abcd[abcd['ROW_NUM_TMP'] == 1]
            abcd.drop(['ROW_NUM_TMP'], axis=1, inplace=True)
            abcd.drop(['WORK_TIME_2'], axis=1, inplace=True)
            def __cal_PRICE_2(x):
                rst = 0
                if x.SUM_WT / x.SUM_ROW == 0:
                    rst = 0
                else:
                    rst = x.SUM_FAC_AMT * 1 / x.SUM_WT * x.SUM_ROW
                return rst

            abcd['PRICE_NEW1'] = abcd.apply(lambda x: __cal_PRICE_2(x), axis=1)
            abcd.drop(['SUM_FAC_AMT'], axis=1, inplace=True)
            abcd.drop(['SUM_WT'], axis=1, inplace=True)
            abcd.drop(['SUM_ROW'], axis=1, inplace=True)
            abcd.drop(['WORK_TIME'], axis=1, inplace=True)
            abcd.drop(['IN_MAT_NO'], axis=1, inplace=True)
            abcd.rename(columns={'MAT_NO': 'INMAT'}, inplace=True)
            self.logger.info(abcd)
            v = ['INMAT']
            df_DP0102LS_0 = pd.merge(df_DP0102LS, abcd, on=v, how='left')
            #df_DP0102LS_1.drop(['INMAT'], axis=1, inplace=True)
            # df_DP0102LS_0['INMAT'] = df_DP0102LS_0['INMAT'].astype(str)
            # tmp_l = df_DP0102LS_0['INMAT'].values.tolist()
            # r = "'%s'" % ("','".join(tmp_l))
            sql = " select ENTRY_COILNO as IN_MAT_NO, PRODUCE_TIME as WORK_TIME ,PROD_COILNO as MAT_NO, (CASE WHEN AVG(OUTPUT_WT)=0 THEN 0 ELSE SUM(COALESCE(DEPT_PROC_STD_AMT,0))*1.0000000000000/AVG(OUTPUT_WT) END) as PRICE_NEW2 FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 WHERE 1=1 AND CALC_END = 'Y' AND DATA_TYPE='%s' AND PRODUCE_TIME<='%s' AND PRODUCE_TIME>='%s' GROUP BY ENTRY_COILNO, PRODUCE_TIME ,PROD_COILNO " % (
                self.data_type, self.account_period_end, e)
            # sql = " select ENTRY_COILNO, PRODUCE_TIME ,PROD_COILNO as INMAT, (CASE WHEN AVG(OUTPUT_WT)=0 THEN 0 ELSE SUM(COALESCE(DEPT_PROC_STD_AMT,0))*1.0000000000000/AVG(OUTPUT_WT) END) as PRICE_NEW2 FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 WHERE 1=1 AND CALC_END = 'Y' AND DATA_TYPE='%s' AND PROD_COILNO IN (%s) GROUP BY ENTRY_COILNO, PRODUCE_TIME ,PROD_COILNO " % (
            #     self.data_type, r)
            self.logger.info(sql)
            df_CA0101data = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()

            df_CA0101data.columns = df_CA0101data.columns.str.upper()
            df_CA0101data['WORK_TIME_2'] = df_CA0101data['WORK_TIME'].astype(float)
            v = ['MAT_NO']
            df_CA0101data['ROW_NUM_TMP'] = df_CA0101data.groupby(v)['WORK_TIME_2'].rank(ascending=False, method='first')

            df_CA0101data = df_CA0101data[df_CA0101data['ROW_NUM_TMP'] == 1]
            df_CA0101data.drop(['ROW_NUM_TMP'], axis=1, inplace=True)
            df_CA0101data.drop(['WORK_TIME_2'], axis=1, inplace=True)

            df_CA0101data.drop(['IN_MAT_NO'], axis=1, inplace=True)
            df_CA0101data.drop(['WORK_TIME'], axis=1, inplace=True)
            df_CA0101data.rename(columns={'MAT_NO': 'INMAT'}, inplace=True)

            self.logger.info(df_CA0101data)
            w = ['INMAT']

            df_DP0102LS_1 = pd.merge(df_DP0102LS_0, df_CA0101data, on=w, how='left')
            df_DP0102LS_1.drop(['INMAT'], axis=1, inplace=True)

            # if df_DP0102LS_N.empty is False:
            #     #df_DP0102LS_N = df_DP0102LS[df_DP0102LS['TYPE'] == 'N']
            #
            #     df_CA0101_Y = df_CA0101[df_CA0101['TYPE'] == 'Y']
            #     df_CA0101_Y['ROW'] = 1
            #     df_CA0101_Y['INMAT'] = df_CA0101_Y['MAT_NO']
            #     df_DP0102LS['INMAT'] = df_DP0102LS['IN_MAT_NO']
            #     w = ['INMAT']
            #
            #     a = df_CA0101_Y.groupby(w)['FAC_AMT'].agg([np.sum]).round(2)
            #     a.rename(columns={'sum': 'SUM_FAC_AMT'}, inplace=True)
            #     b = df_CA0101_Y.groupby(w)['ROW'].agg([np.sum]).round(2)
            #     b.rename(columns={'sum': 'SUM_ROW'}, inplace=True)
            #     c = df_CA0101_Y.groupby(w)['WT'].agg([np.sum]).round(2)
            #     c.rename(columns={'sum': 'SUM_WT'}, inplace=True)
            #     ab = pd.merge(a, b, on=w, how='left')
            #     abc = pd.merge(ab, c, on=w, how='left')
            #
            #     def __cal_PRICE_2(x):
            #         rst = 0
            #         if x.SUM_WT / x.SUM_ROW == 0:
            #             rst = 0
            #         else:
            #             rst = x.SUM_FAC_AMT * 1 / x.SUM_WT * x.SUM_ROW
            #         return rst
            #
            #     abc['PRICE_NEW'] = abc.apply(lambda x: __cal_PRICE_2(x), axis=1)
            #     abc.drop(['SUM_FAC_AMT'], axis=1, inplace=True)
            #     abc.drop(['SUM_WT'], axis=1, inplace=True)
            #     abc.drop(['SUM_ROW'], axis=1, inplace=True)
            #     self.logger.info(abc)
            #
            #     df_DP0102LS_1 = pd.merge(df_DP0102LS, abc, on=w, how='left')
            #     df_DP0102LS_1.drop(['INMAT'], axis=1, inplace=True)
            # else:
            #     sql = " select PROD_COILNO as INMAT, (CASE WHEN AVG(OUTPUT_WT)=0 THEN 0 ELSE SUM(COALESCE(DEPT_PROC_STD_AMT,0))*1.0000000000000/AVG(OUTPUT_WT) END) as PRICE_NEW FROM BGTARAS1.T_ADS_FACT_SICB_CA0001 WHERE 1=1 AND CALC_END = 'Y' AND DATA_TYPE='%s' AND PRODUCE_TIME<='%s' AND PRODUCE_TIME>='%s' GROUP BY ENTRY_COILNO,PROD_COILNO,PRODUCE_TIME " % (
            #         self.data_type, self.account_period_end, e)
            #     self.logger.info(sql)
            #     df_CA0101data = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
            #
            #
            #     df_CA0101data.columns = df_CA0101data.columns.str.upper()
            #
            #     df_DP0102LS['INMAT'] = df_DP0102LS['IN_MAT_NO']
            #     self.logger.info(df_CA0101data)
            #     w = ['INMAT']
            #
            #     df_DP0102LS_1 = pd.merge(df_DP0102LS, df_CA0101data, on=w, how='left')
            #     df_DP0102LS_1.drop(['INMAT'], axis=1, inplace=True)
            #
            # self.logger.info(df_DP0102LS_1)



            df_DP0102LS_1.rename(columns={'PRICE': 'PRICE_OLD'}, inplace=True)
            df_DP0102LS_1['PRICE_NEW1'].fillna(value='', inplace=True)
            # df_DP0102LS_1['PRICE_NEW1'] = ''
            # df_DP0102LS_1['PRICE_NEW2'] = ''
            df_DP0102LS_1['PRICE_NEW2'].fillna(value='', inplace=True)
            def __cal_PRICE_NEW3(x):

                if x.PRICE_NEW2 == 0 :
                    rst = ''
                else:
                    rst = x.PRICE_NEW2

                return rst

            df_DP0102LS_1['PRICE_NEW3'] = df_DP0102LS_1.apply(lambda x: __cal_PRICE_NEW3(x), axis=1)
            df_DP0102LS_1.drop(['PRICE_NEW2'], axis=1, inplace=True)
            def __cal_PRICE_3(x):
                rst = 0
                if x.PRICE_NEW1 != '' :
                    rst = x.PRICE_NEW1
                    #rst = x.PRICE_OLD
                if x.PRICE_NEW1 == '' and x.PRICE_NEW3 != '':
                    rst = x.PRICE_NEW3
                    #rst = x.PRICE_OLD
                if x.PRICE_NEW1 == '' and x.PRICE_NEW3 == '':
                    rst = x.PRICE_OLD
                return rst

            df_DP0102LS_1['PRICE'] = df_DP0102LS_1.apply(lambda x: __cal_PRICE_3(x), axis=1)
            df_DP0102LS_1.rename(columns={'TYPE': 'TYPE_OLD'}, inplace=True)

            def __cal_TYPE_3(x):
                rst = 0

                if x.PRICE_NEW1 != '' :
                    rst = 'Y'
                if x.PRICE_NEW1 == '' and x.PRICE_NEW3 != '':
                    rst = 'Y'
                if x.PRICE_NEW1 == '' and x.PRICE_NEW3 == '':
                    rst = 'Y'
                return rst


            df_DP0102LS_1['TYPE'] = df_DP0102LS_1.apply(lambda x: __cal_TYPE_3(x), axis=1)

            df_DP0102LS_1.drop(['TYPE_OLD'], axis=1, inplace=True)
            df_DP0102LS_1.drop(['PRICE_NEW1'], axis=1, inplace=True)
            df_DP0102LS_1.drop(['PRICE_NEW3'], axis=1, inplace=True)
            df_DP0102LS_1.drop(['PRICE_OLD'], axis=1, inplace=True)


            def __cal_ACT_AMT_2(x):
                rst = 0
                str1 = 'PN'
                if str1 not in x.APP_THROW_AI_MODE:
                    rst = x.IN_WT * x.PRICE

                else:
                    rst = x.ACT_N * x.PRICE
                return rst

            df_DP0102LS_1['ACT_AMT_NEW'] = df_DP0102LS_1.apply(lambda x: __cal_ACT_AMT_2(x), axis=1)

            def __cal_UNIT_AMT_2(x):
                rst = x.ACT_N * x.PRICE

                return rst

            df_DP0102LS_1['UNIT_AMT_NEW'] = df_DP0102LS_1.apply(lambda x: __cal_UNIT_AMT_2(x), axis=1)

            def __cal_FAC_AMT_2(x):
                rst = 0
                str1 = 'PN'
                if str1 not in x.APP_THROW_AI_MODE:
                    rst = x.IN_WT * x.PRICE + x.ACT_N * x.PRICE

                else:
                    rst = x.ACT_N * x.PRICE
                return rst

            df_DP0102LS_1['FAC_AMT_NEW'] = df_DP0102LS_1.apply(lambda x: __cal_FAC_AMT_2(x), axis=1)
            df_DP0102LS_1.rename(columns={'ACT_AMT': 'ACT_AMT_OLD'}, inplace=True)
            df_DP0102LS_1.rename(columns={'UNIT_AMT': 'UNIT_AMT_OLD'}, inplace=True)
            df_DP0102LS_1.rename(columns={'FAC_AMT': 'FAC_AMT_OLD'}, inplace=True)
            def __cal_ACT_AMT_3(x):
                rst = 0
                if x.ACT_AMT_NEW is None:
                    rst = x.ACT_AMT_OLD
                else:
                    rst = x.ACT_AMT_NEW
                return rst

            df_DP0102LS_1['ACT_AMT'] = df_DP0102LS_1.apply(lambda x: __cal_ACT_AMT_3(x), axis=1)
            df_DP0102LS_1.drop(['ACT_AMT_NEW'], axis=1, inplace=True)
            df_DP0102LS_1.drop(['ACT_AMT_OLD'], axis=1, inplace=True)
            def __cal_UNIT_AMT_3(x):
                rst = 0
                if x.UNIT_AMT_NEW is None:
                    rst = x.UNIT_AMT_OLD
                else:
                    rst = x.UNIT_AMT_NEW
                return rst

            df_DP0102LS_1['UNIT_AMT'] = df_DP0102LS_1.apply(lambda x: __cal_UNIT_AMT_3(x), axis=1)
            df_DP0102LS_1.drop(['UNIT_AMT_NEW'], axis=1, inplace=True)
            df_DP0102LS_1.drop(['UNIT_AMT_OLD'], axis=1, inplace=True)
            def __cal_FAC_AMT_3(x):
                rst = 0
                if x.FAC_AMT_NEW is None:
                    rst = x.FAC_AMT_OLD
                else:
                    rst = x.FAC_AMT_NEW
                return rst

            df_DP0102LS_1['FAC_AMT'] = df_DP0102LS_1.apply(lambda x: __cal_FAC_AMT_3(x), axis=1)
            df_DP0102LS_1.drop(['FAC_AMT_NEW'], axis=1, inplace=True)
            df_DP0102LS_1.drop(['FAC_AMT_OLD'], axis=1, inplace=True)



            df_CA0101FINAL = pd.concat([df_DP0101, df_DP0102LS_1], ignore_index=True)
            self.logger.info(df_CA0101FINAL)
            #df_CA0101FINAL.drop(['CONSUME'], axis=1, inplace=True)

            def __cal_CONSUME_T(x):
                rst = 0
                if x.ACT_WT == 0:
                    rst = 0
                else:
                    rst = x.ACT_N * 1.000000 / x.ACT_WT
                    rst = abs(rst)
                return rst

            df_CA0101FINAL['CONSUME'] = df_CA0101FINAL.apply(lambda x: __cal_CONSUME_T(x), axis=1)

            def __cal_PRICE_T(x):
                rst = 0
                if x.ACT_WT == 0:
                    rst = 0
                else:
                    rst = x.UNIT_AMT / x.ACT_WT
                return rst

            df_CA0101FINAL['PRICE_T'] = df_CA0101FINAL.apply(lambda x: __cal_PRICE_T(x), axis=1)
            df_CA0101FINAL['WCE_NAME'] = '0'
            df_CA0101FINAL['WCE_UNIT'] = '0'

            def __cal_WCE_STR(x):
                rst = str(x.WCE)
                return rst

            df_CA0101FINAL['WCE_STR'] = df_CA0101FINAL.apply(lambda x: __cal_WCE_STR(x), axis=1)

            self.logger.info(df_CA0101FINAL)
            self.logger.info('-------------')
            df_CA0101FINAL_1545 = df_CA0101FINAL[(df_CA0101FINAL['WCE_STR'].str[0:2] == '15') | (df_CA0101FINAL['WCE_STR'].str[0:1] == '4') | (df_CA0101FINAL['WCE_STR'].str[0:1] == '5')]

            #df_CA0101FINAL_1545 = df_CA0101FINAL[(str(df_CA0101FINAL['WCE'])[0:2] == '15') | (str(df_CA0101FINAL['WCE'])[0:1] == '4') | (str(df_CA0101FINAL['WCE'])[0:1] == '5')]

            df_CA0101FINAL_18110 = df_CA0101FINAL[df_CA0101FINAL['WCE_STR'] == '18110']
            df_CA0101FINAL_else = df_CA0101FINAL[(df_CA0101FINAL['WCE_STR'].str[0:2] != '15') & (df_CA0101FINAL['WCE_STR'].str[0:1] != '4') & (df_CA0101FINAL['WCE_STR'].str[0:1] != '5')  & (df_CA0101FINAL['WCE_STR'] != '18110') ]

            #df_CA0101FINAL_else = df_CA0101FINAL[str(df_CA0101FINAL['WCE'])[0:2] != '15' and str(df_CA0101FINAL['WCE'])[0:1] not in ['4', '5' '8'] and str(df_CA0101FINAL['WCE']) not in ['18110', '81900', '20000', '30310', '30700', '30600', '30900']]


            # sql = " SELECT " \
            #       " YEDITION" \
            #       " FROM BGRAGGCB.WH_CY00_000010" \
            #       " WHERE 1=1 " \
            #       " AND ACCOUNT = '%s'" \
            #       " AND YEAR=LEFT('%s',4)" \
            #       " AND MONTH_START<=SUBSTR('%s',5,2) " \
            #       " AND MONTH_END>=SUBSTR('%s',5,2) " \
            #       " ORDER BY MONTH_END ASC,MONTH_START DESC,EDITION DESC " \
            #       " FETCH FIRST 1 ROWS ONLY" % (self.account, self.account_period_end, self.account_period_end, self.account_period_end)
            # self.logger.info(sql)
            # df_YEDITION = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            # df_YEDITION.columns = df_YEDITION.columns.str.upper()
            # SOURCE_YEDITION = df_YEDITION.loc[0]['YEDITION']

            #sql = " select VAL AS B_CONSUME,ITEM_ID,JHZ_KEY FROM BGRAGGCB.SU_AJBG_DP0501  WHERE END_T IS NULL"
            sql = " select REFER_VALUE AS B_CONSUME,PROJ_CODE AS ITEM_ID,PLAN_SYS_KV_CODE AS JHZ_KEY FROM BGTAMAS1.T_ADS_FACT_SICB_DP0501  WHERE END_DATE IS NULL"
            self.logger.info(sql)
            #df_DP0501 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            df_DP0501 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()

            df_DP0501.columns = df_DP0501.columns.str.upper()
            w = ['ITEM_ID',
                 'JHZ_KEY']
            df_CA0101FINAL_1545_NEW = pd.merge(df_CA0101FINAL_1545, df_DP0501, on=w, how='left')
            df_CA0101FINAL_else['B_CONSUME'] = df_CA0101FINAL_else['CONSUME']

            # sql = " select BASIC_STD AS B_CONSUME,COST_CENTER,WCE  FROM BGRAGGCB.SU_AJBG_DP0502 WHERE YEDITION='%s' AND ACCOUNT='%s'" % (SOURCE_YEDITION, self.account)
            # self.logger.info(sql)
            # df_DP0502 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            # df_DP0502.columns = df_DP0502.columns.str.upper()
            # w = ['COST_CENTER',
            #      'WCE']
            # df_CA0101FINAL_else_NEW = pd.merge(df_CA0101FINAL_else, df_DP0502, on=w, how='left')

            # sql = " select BASIC_STD AS B_CONSUME,COST_CENTER,PRODUCT_CODE,IN_PRODUCT_CODE,WCE FROM BGRAGGCB.SU_AJBG_DP0502 WHERE YEDITION='%s' AND ACCOUNT='%s'" % (SOURCE_YEDITION, self.account)
            #
            # self.logger.info(sql)
            # df_DP0502_2 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            # df_DP0502_2.columns = df_DP0502_2.columns.str.upper()
            # w = ['COST_CENTER',
            #      'PRODUCT_CODE',
            #      'IN_PRODUCT_CODE',
            #      'WCE']
            # df_CA0101FINAL_18110_NEW = pd.merge(df_CA0101FINAL_18110, df_DP0502_2, on=w, how='left')

            def __cal_B_CONSUME_18(x):

                if x.ACT_WT > 0:
                    rst = -1 * x.CONSUME
                else:
                    rst = x.CONSUME

                return rst

            df_CA0101FINAL_18110['B_CONSUME'] = df_CA0101FINAL_18110.apply(lambda x: __cal_B_CONSUME_18(x), axis=1)

            ##df_CA0101FINAL_other['B_CONSUME'] = df_CA0101FINAL_other['CONSUME']
            df_CA0101FINAL_NEW = pd.concat([df_CA0101FINAL_1545_NEW, df_CA0101FINAL_else, df_CA0101FINAL_18110], ignore_index=True)
            self.logger.info(df_CA0101FINAL_NEW)
            df_CA0101FINAL_NEW['B_CONSUME'].fillna(value='', inplace=True)

            def __cal_B_CONSUME_OTHER(x):

                if x.B_CONSUME == '':
                    rst = x.CONSUME
                else:

                    rst = x.B_CONSUME
                return rst

            df_CA0101FINAL_NEW['B_CONSUME'] = df_CA0101FINAL_NEW.apply(lambda x: __cal_B_CONSUME_OTHER(x), axis=1)



            df_NEW15 = df_CA0101FINAL_NEW[df_CA0101FINAL_NEW['WCE_STR'].str[0:2] == '15']
            df_NEWN15 = df_CA0101FINAL_NEW[df_CA0101FINAL_NEW['WCE_STR'].str[0:2] != '15']

            df_NEWN15['B_ACT_N'] = df_NEWN15['B_CONSUME'] * df_NEWN15['ACT_WT']
            df_NEWN15['B_ACT_AMT'] = 0
            df_NEWN15['B_UNIT_AMT'] = df_NEWN15['B_CONSUME'] * df_NEWN15['ACT_WT'] * df_NEWN15['PRICE']
            df_NEWN15['B_FAC_AMT'] = df_NEWN15['B_CONSUME'] * df_NEWN15['ACT_WT'] * df_NEWN15['PRICE']
            df_NEWN15['PRICE_TB'] = df_NEWN15['B_CONSUME'] * df_NEWN15['PRICE']

            df_NEW15['B_ACT_N'] = df_NEW15['B_CONSUME'] * df_NEW15['ACT_WT']
            def __cal_B_ACT_AMT_2(x):
                rst = 0
                str1 = 'PN'
                if str1 not in x.APP_THROW_AI_MODE:
                    rst = x.IN_WT * x.PRICE
                else:
                    rst = x.CONSUME * x.ACT_WT * x.PRICE
                return rst

            df_NEW15['B_ACT_AMT'] = df_NEW15.apply(lambda x: __cal_B_ACT_AMT_2(x), axis=1)
            df_NEW15['B_UNIT_AMT'] = df_NEW15['B_CONSUME'] * df_NEW15['ACT_WT'] * df_NEW15['PRICE']

            def __cal_B_B_FAC_AMT_2(x):
                rst = 0
                str1 = 'PN'
                if str1 not in x.APP_THROW_AI_MODE:
                    rst = x.IN_WT * x.PRICE + x.ACT_N * x.PRICE

                else:
                    rst = x.CONSUME * x.ACT_WT * x.PRICE
                return rst

            df_NEW15['B_FAC_AMT'] = df_NEW15.apply(lambda x: __cal_B_B_FAC_AMT_2(x), axis=1)
            df_NEW15['PRICE_TB'] = df_NEW15['B_CONSUME'] * df_NEW15['PRICE']
            df_CA0101FINAL_NEWEND = pd.concat([df_NEWN15, df_NEW15], ignore_index=True)
            df_CA0101FINAL_NEWEND.drop(['WCE_STR'], axis=1, inplace=True)
            df_CA0101FINAL_NEWEND.drop(['EDITION'], axis=1, inplace=True)
            df_CA0101FINAL_NEWEND.drop(['FLAG'], axis=1, inplace=True)
            self.logger.info(df_CA0101FINAL_NEWEND)

            df_CA0101FINAL_NEWEND.drop(['REC_ID'], axis=1, inplace=True)
            #df_CA0101FINAL_NEWEND['REC_CREATOR'] = df_CA0101FINAL_NEWEND['REC_REVISOR']
            #df_CA0101FINAL_NEWEND['REC_CREATE_TIME'] = df_CA0101FINAL_NEWEND['REC_REVISOR_TIME']

            df_CA0101FINAL_NEWEND.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'TEAM': 'CLASS'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'WIDTH_COD': 'WIDTH_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'THICK_COD': 'THICK_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'JHZ_KEY': 'PLAN_SYS_KV_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'JHZ_KEY_NAME': 'PLAN_SYS_KV_CODE_NAME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'WCE_NAME': 'COST_SUBJECT_CNAME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'WCE_UNIT': 'COST_SUBJECT_UNIT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'ACT_AMT': 'PROC_ON_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'UNIT_AMT': 'UNIT_PROC_ON_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'FAC_AMT': 'DEPT_PROC_ON_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PRICE_T': 'TON_STEEL_PRICE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PRICE': 'COST_SUBJECT_PRICE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'B_CONSUME': 'STD_UNITCONSUME'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'B_ACT_N': 'COST_SUBJECT_STD_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'B_ACT_AMT': 'PROC_STD_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'B_UNIT_AMT': 'UNIT_PROC_STD_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'B_FAC_AMT': 'DEPT_PROC_STD_AMT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PRICE_TB': 'STD_TON_STEEL_PRICE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'TYPE': 'CALC_END'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_WIDTH': 'ENTRY_WIDTH'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_WIDTH_COD': 'ENTRY_WIDTH_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_THICK': 'ENTRY_THICK'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_THICK_COD': 'ENTRY_THICK_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'PLAN_NO_COD': 'PLAN_NO_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'TRIM_WIDTH_C': 'TRIMMING_WIDTH_RANGE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'TRIM_WIDTH_COD': 'TRIMMING_WIDTH_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'QS_CODE': 'PROD_CAT_CODE'}, inplace=True)
            df_CA0101FINAL_NEWEND.rename(columns={'QS_NAME': 'PROD_CAT'}, inplace=True)
            # NOTE df.fillna(value=None, method=None, axis=None, inplace=False, limit=None, downcast=None)
            # NOTE https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html
            #df_CA0101FINAL_NEWEND['UNIT_PROC_STD_AMT'].fillna(value=0.0, inplace=True)
            #df_CA0101FINAL_NEWEND['DEPT_PROC_STD_AMT'].fillna(value=0.0, inplace=True)
            #df_CA0101FINAL_NEWEND['STD_TON_STEEL_PRICE'].fillna(value=0.0, inplace=True)
            #df_CA0101FINAL_NEWEND['STD_UNITCONSUME'].fillna(value=0.0, inplace=True)
            #df_CA0101FINAL_NEWEND['COST_SUBJECT_STD_AMT'].fillna(value=0.0, inplace=True)
            # NOTE drop 要把这几个全drop, 因为我多select了 这三个
            #exclude_columns = df_CA0101FINAL_NEWEND['PARM_1']
            #for c in exclude_columns:
            #    df_CA0101FINAL_NEWEND.drop([c], axis=1, inplace=True)
            
            now = datetime.datetime.now()
            now_1 = now.strftime('%Y%m%tmp_dict%H%M%S')
            df_CA0101FINAL_NEWEND['REC_REVISOR'] = 'BGRAGGCB'
            df_CA0101FINAL_NEWEND['REC_REVISOR_TIME'] = now_1
            df_CA0101FINAL_NEWEND['REC_CREATOR'] = 'BGRAGGCB'
            df_CA0101FINAL_NEWEND['REC_CREATE_TIME'] = now_1


            if self.data_type == 'M':
                XRetryableSave(p_db_conn=self.db_conn_mpp, p_table_name='T_ADS_FACT_SICB_CA0001_M', p_schema='BGTAMAS1',
                               p_dataframe=df_CA0101FINAL_NEWEND,
                               p_max_times=5).redo()
            else:
                XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_CA0001', p_schema='BGTARAS1',
                               p_dataframe=df_CA0101FINAL_NEWEND,
                               p_max_times=5).redo()





        else:
            return



        super(CA0101Job, self).do_execute()
    def __step_0(self):
        sql = " DELETE FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_CA0001" \
              " WHERE 1=1 " \
              " AND LEFT(PRODUCE_TIME,14) >= '%s'" \
              " AND LEFT(PRODUCE_TIME,14) < '%s'" \
              " AND ACCT = '%s'" \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE = '%s'" \
              " AND DATA_TYPE = '%s'" % (
                  self.account_period_start,
                  self.account_period_end,
                  self.account,
                  self.cost_center,
                  self.unit,
                  self.data_type)
        self.logger.info(sql)
        self.db_conn_rds.execute(sql)
